package action;

import java.util.LinkedHashMap;
import java.util.List;
import java.util.Map;

import com.opensymphony.xwork2.ActionContext;

import basic.PageInfo;
import basic.QuerySet;
import basic.ResultSet;
import common.BaseAction;
import interfaces.IGlobalService;

public class FormAction extends BaseAction {

	/**
	 * 
	 */
	private static final long serialVersionUID = 7507534255609348887L;

	private IGlobalService globalService;
	private Map<String, Object> formMap = new LinkedHashMap<String, Object>();
	private List<LinkedHashMap<String, Object>> formList;

	public String day() throws Exception {
		StringBuffer sb = new StringBuffer();
		sb.append(" select ");
		sb.append(" DATE_FORMAT(txn_date,'%Y-%m-%d') 'txnDate', ");
		sb.append(" LEFT(txn_no,6) 'subNo',");
		sb.append(" count(case when txn_state='1' then 1 else null end) 'stateCount', ");
		sb.append(" sum(case when txn_state='1' then  txn_amount else 0 end) 'stateTotal', ");
		sb.append(" count(case when txn_refund='1' then 1 else null end) 'refundCount', ");
		sb.append(" sum(case when txn_refund='1' then  txn_amount else 0 end) 'refundTotal', ");
		sb.append(" count(case when txn_refuse='1' then 1 else null end) 'refuseCount', ");
		sb.append(" sum(case when txn_refuse='1' then  txn_amount else 0 end) 'refuseTotal', ");
		sb.append(" count(case when txn_freeze='1' then 1 else null end) 'freezeCount', ");
		sb.append(" sum(case when txn_freeze='1' then  txn_amount else 0 end) 'freezeTotal' ");
		sb.append(" from t_transaction ");
		sb.append(" where txn_no like CONCAT('" + ActionContext.getContext().getSession().get("query").toString() + "','%') and DATE_SUB(CURDATE(), INTERVAL 30 DAY) <= txn_date ");
		sb.append(" group by LEFT(txn_no,6),DATE_FORMAT(txn_date,'%Y-%m-%d') ");
		sb.append(" order by DATE_FORMAT(txn_date,'%Y-%m-%d') desc , LEFT(txn_no,6) asc ");
		QuerySet<Object> querySet = new QuerySet<Object>();
		querySet.setSqlInfo(sb.toString());
		querySet.setPageInfo(new PageInfo(1, Integer.MAX_VALUE));
		ResultSet<LinkedHashMap<String, Object>> resultSet = globalService.search(querySet);
		formList = resultSet.getResultList();
		pageInfo = resultSet.getPageInfo();
		return SUCCESS;
	}

	public String month() throws Exception {
		StringBuffer sb = new StringBuffer();
		sb.append(" select ");
		sb.append(" DATE_FORMAT(txn_date,'%Y-%m') 'txnDate', ");
		sb.append(" LEFT(txn_no,6) 'subNo',");
		sb.append(" count(case when txn_state='1' then 1 else null end) 'stateCount', ");
		sb.append(" sum(case when txn_state='1' then  txn_amount else 0 end) 'stateTotal', ");
		sb.append(" count(case when txn_refund='1' then 1 else null end) 'refundCount', ");
		sb.append(" sum(case when txn_refund='1' then  txn_amount else 0 end) 'refundTotal', ");
		sb.append(" count(case when txn_refuse='1' then 1 else null end) 'refuseCount', ");
		sb.append(" sum(case when txn_refuse='1' then  txn_amount else 0 end) 'refuseTotal', ");
		sb.append(" count(case when txn_freeze='1' then 1 else null end) 'freezeCount', ");
		sb.append(" sum(case when txn_freeze='1' then  txn_amount else 0 end) 'freezeTotal' ");
		sb.append(" from t_transaction ");
		sb.append(" where txn_no like CONCAT('" + ActionContext.getContext().getSession().get("query").toString() + "','%') and DATE_SUB(CURDATE(), INTERVAL 360 DAY) <= txn_date ");
		sb.append(" group by LEFT(txn_no,6),DATE_FORMAT(txn_date,'%Y-%m') ");
		sb.append(" order by DATE_FORMAT(txn_date,'%Y-%m') desc , LEFT(txn_no,6) asc ");
		QuerySet<Object> querySet = new QuerySet<Object>();
		querySet.setSqlInfo(sb.toString());
		querySet.setPageInfo(new PageInfo(1, Integer.MAX_VALUE));
		ResultSet<LinkedHashMap<String, Object>> resultSet = globalService.search(querySet);
		formList = resultSet.getResultList();
		pageInfo = resultSet.getPageInfo();
		return SUCCESS;
	}

	public String website() throws Exception {
		StringBuffer sb = new StringBuffer();
		sb.append(" select ");
		sb.append(" txn_site 'txnSite', ");
		sb.append(" LEFT(txn_no,6) 'subNo',");
		sb.append(" count(case when txn_state='1' then 1 else null end) 'stateCount', ");
		sb.append(" sum(case when txn_state='1' then  txn_amount else 0 end) 'stateTotal'");
		sb.append(" from t_transaction ");
		sb.append(" where txn_no like CONCAT('" + ActionContext.getContext().getSession().get("query").toString() + "','%')  and txn_state='1' and DATE_SUB(CURDATE(), INTERVAL 360 DAY) <= txn_date");
		sb.append(" group by LEFT(txn_no,6),txn_site ");
		sb.append(" order by  LEFT(txn_no,6) asc ");
		QuerySet<Object> querySet = new QuerySet<Object>();
		querySet.setSqlInfo(sb.toString());
		querySet.setPageInfo(new PageInfo(1, Integer.MAX_VALUE));
		ResultSet<LinkedHashMap<String, Object>> resultSet = globalService.search(querySet);
		formList = resultSet.getResultList();
		pageInfo = resultSet.getPageInfo();
		return SUCCESS;
	}

	public String today() throws Exception {
		QuerySet<Object> querySet = new QuerySet<Object>();
		StringBuffer sb = new StringBuffer();

		querySet = new QuerySet<Object>();
		new StringBuffer();
		sb.append(" select count(distinct crd.crd_cemail)   'txnGross'");
		sb.append(" from t_transaction txn join t_card crd on txn.crd_no=crd.crd_no  ");
		sb.append(" where txn_no like CONCAT('" + ActionContext.getContext().getSession().get("query").toString() + "','%') ");
		sb.append(" and DATE_FORMAT(txn_date,'%Y-%m-%d')=DATE_FORMAT(CURDATE(),'%Y-%m-%d') ");
		querySet.setSqlInfo(sb.toString());
		querySet.setPageInfo(new PageInfo(1, 1));
		formMap.put("txnGross", globalService.search(querySet).getResultList().get(0).get("txnGross"));
		
		querySet = new QuerySet<Object>();
		sb = new StringBuffer();
		sb.append(" select count(*)   'txnCount'");
		sb.append(" from t_transaction txn join t_card crd on txn.crd_no=crd.crd_no  ");
		sb.append(" where txn_no like CONCAT('" + ActionContext.getContext().getSession().get("query").toString() + "','%') ");
		sb.append(" and txn_state ='1' ");
		sb.append(" and DATE_FORMAT(txn_date,'%Y-%m-%d')=DATE_FORMAT(CURDATE(),'%Y-%m-%d') ");
		querySet.setSqlInfo(sb.toString());
		querySet.setPageInfo(new PageInfo(1, 1));
		formMap.put("txnCount", globalService.search(querySet).getResultList().get(0).get("txnCount"));
		
		querySet = new QuerySet<Object>();
		sb = new StringBuffer();
		sb.append(" select IFNULL(sum(txn_amount),0)   'txnTotal'");
		sb.append(" from t_transaction txn join t_card crd on txn.crd_no=crd.crd_no  ");
		sb.append(" where txn_no like CONCAT('" + ActionContext.getContext().getSession().get("query").toString() + "','%') ");
		sb.append(" and txn_state ='1' ");
		sb.append(" and DATE_FORMAT(txn_date,'%Y-%m-%d')=DATE_FORMAT(CURDATE(),'%Y-%m-%d') ");
		querySet.setSqlInfo(sb.toString());
		querySet.setPageInfo(new PageInfo(1, 1));
		formMap.put("txnTotal", globalService.search(querySet).getResultList().get(0).get("txnTotal"));
		
		querySet = new QuerySet<Object>();
		sb = new StringBuffer();
		sb.append(" select count(*)   'txnRefund'");
		sb.append(" from t_transaction txn join t_card crd on txn.crd_no=crd.crd_no  ");
		sb.append(" where txn_no like CONCAT('" + ActionContext.getContext().getSession().get("query").toString() + "','%') ");
		sb.append(" and txn_refund ='1' ");
		sb.append(" and DATE_FORMAT(txn_date,'%Y-%m-%d')=DATE_FORMAT(CURDATE(),'%Y-%m-%d') ");
		querySet.setSqlInfo(sb.toString());
		querySet.setPageInfo(new PageInfo(1, 1));
		formMap.put("txnRefund", globalService.search(querySet).getResultList().get(0).get("txnRefund"));
		
		querySet = new QuerySet<Object>();
		sb = new StringBuffer();
		sb.append(" select count(*)   'txnRefuse'");
		sb.append(" from t_transaction txn join t_card crd on txn.crd_no=crd.crd_no  ");
		sb.append(" where txn_no like CONCAT('" + ActionContext.getContext().getSession().get("query").toString() + "','%') ");
		sb.append(" and txn_refuse ='1' ");
		sb.append(" and DATE_FORMAT(txn_date,'%Y-%m-%d')=DATE_FORMAT(CURDATE(),'%Y-%m-%d') ");
		querySet.setSqlInfo(sb.toString());
		querySet.setPageInfo(new PageInfo(1, 1));
		formMap.put("txnRefuse", globalService.search(querySet).getResultList().get(0).get("txnRefuse"));

		return SUCCESS;
	}

	public String yesterday() throws Exception {
		
		QuerySet<Object> querySet = new QuerySet<Object>();
		StringBuffer sb = new StringBuffer();

		querySet = new QuerySet<Object>();
		new StringBuffer();
		sb.append(" select count(distinct crd.crd_cemail)   'txnGross'");
		sb.append(" from t_transaction txn join t_card crd on txn.crd_no=crd.crd_no  ");
		sb.append(" where txn_no like CONCAT('" + ActionContext.getContext().getSession().get("query").toString() + "','%') ");
		sb.append(" and DATE_FORMAT(txn_date,'%Y-%m-%d')=DATE_FORMAT( DATE_SUB(CURDATE(),INTERVAL 1 DAY),'%Y-%m-%d') ");
		querySet.setSqlInfo(sb.toString());
		querySet.setPageInfo(new PageInfo(1, 1));
		formMap.put("txnGross", globalService.search(querySet).getResultList().get(0).get("txnGross"));

		querySet = new QuerySet<Object>();
		sb = new StringBuffer();
		sb.append(" select count(*)   'txnCount'");
		sb.append(" from t_transaction txn join t_card crd on txn.crd_no=crd.crd_no  ");
		sb.append(" where txn_no like CONCAT('" + ActionContext.getContext().getSession().get("query").toString() + "','%') ");
		sb.append(" and txn_state ='1' ");
		sb.append(" and DATE_FORMAT(txn_date,'%Y-%m-%d')=DATE_FORMAT( DATE_SUB(CURDATE(),INTERVAL 1 DAY),'%Y-%m-%d') ");
		querySet.setSqlInfo(sb.toString());
		querySet.setPageInfo(new PageInfo(1, 1));
		formMap.put("txnCount", globalService.search(querySet).getResultList().get(0).get("txnCount"));
		
		querySet = new QuerySet<Object>();
		sb = new StringBuffer();
		sb.append(" select IFNULL(sum(txn_amount),0)   'txnTotal'");
		sb.append(" from t_transaction txn join t_card crd on txn.crd_no=crd.crd_no  ");
		sb.append(" where txn_no like CONCAT('" + ActionContext.getContext().getSession().get("query").toString() + "','%') ");
		sb.append(" and txn_state ='1' ");
		sb.append(" and DATE_FORMAT(txn_date,'%Y-%m-%d')=DATE_FORMAT( DATE_SUB(CURDATE(),INTERVAL 1 DAY),'%Y-%m-%d') ");
		querySet.setSqlInfo(sb.toString());
		querySet.setPageInfo(new PageInfo(1, 1));
		formMap.put("txnTotal", globalService.search(querySet).getResultList().get(0).get("txnTotal"));
		
		querySet = new QuerySet<Object>();
		sb = new StringBuffer();
		sb.append(" select count(*)   'txnRefund'");
		sb.append(" from t_transaction txn join t_card crd on txn.crd_no=crd.crd_no  ");
		sb.append(" where txn_no like CONCAT('" + ActionContext.getContext().getSession().get("query").toString() + "','%') ");
		sb.append(" and txn_refund ='1' ");
		sb.append(" and DATE_FORMAT(txn_date,'%Y-%m-%d')=DATE_FORMAT( DATE_SUB(CURDATE(),INTERVAL 1 DAY),'%Y-%m-%d') ");
		querySet.setSqlInfo(sb.toString());
		querySet.setPageInfo(new PageInfo(1, 1));
		formMap.put("txnRefund", globalService.search(querySet).getResultList().get(0).get("txnRefund"));
		
		querySet = new QuerySet<Object>();
		sb = new StringBuffer();
		sb.append(" select count(*)   'txnRefuse'");
		sb.append(" from t_transaction txn join t_card crd on txn.crd_no=crd.crd_no  ");
		sb.append(" where txn_no like CONCAT('" + ActionContext.getContext().getSession().get("query").toString() + "','%') ");
		sb.append(" and txn_refuse ='1' ");
		sb.append(" and DATE_FORMAT(txn_date,'%Y-%m-%d')=DATE_FORMAT( DATE_SUB(CURDATE(),INTERVAL 1 DAY),'%Y-%m-%d') ");
		querySet.setSqlInfo(sb.toString());
		querySet.setPageInfo(new PageInfo(1, 1));
		formMap.put("txnRefuse", globalService.search(querySet).getResultList().get(0).get("txnRefuse"));

		return SUCCESS;
		
		
	}
	
public String history() throws Exception {
		
		QuerySet<Object> querySet = new QuerySet<Object>();
		StringBuffer sb = new StringBuffer();

		querySet = new QuerySet<Object>();
		sb = new StringBuffer();
		sb.append(" select count(*)   'txnCount' ");
		sb.append(" from t_transaction txn   ");
		sb.append(" where txn_no like CONCAT('" + ActionContext.getContext().getSession().get("query").toString() + "','%') ");
		sb.append(" and txn_state ='1' ");
		querySet.setSqlInfo(sb.toString());
		querySet.setPageInfo(new PageInfo(1, 1));
		formMap.put("txnCount", globalService.search(querySet).getResultList().get(0).get("txnCount"));
		
		querySet = new QuerySet<Object>();
		sb = new StringBuffer();
		sb.append(" select IFNULL(sum(txn_amount),0)   'txnTotal' ");
		sb.append(" from t_transaction txn ");
		sb.append(" where txn_no like CONCAT('" + ActionContext.getContext().getSession().get("query").toString() + "','%') ");
		sb.append(" and txn_state ='1' ");
		querySet.setSqlInfo(sb.toString());
		querySet.setPageInfo(new PageInfo(1, 1));
		formMap.put("txnTotal", globalService.search(querySet).getResultList().get(0).get("txnTotal"));
		
		querySet = new QuerySet<Object>();
		sb = new StringBuffer();
		sb.append(" select count(*)   'txnSettled' ");
		sb.append(" from t_transaction txn  ");
		sb.append(" where txn_no like CONCAT('" + ActionContext.getContext().getSession().get("query").toString() + "','%') ");
		sb.append(" and txn_settle ='1' ");
		querySet.setSqlInfo(sb.toString());
		querySet.setPageInfo(new PageInfo(1, 1));
		formMap.put("txnSettled", globalService.search(querySet).getResultList().get(0).get("txnSettled"));
		
		querySet = new QuerySet<Object>();
		sb = new StringBuffer();
		sb.append(" select count(*)   'txnUnsettle' ");
		sb.append(" from t_transaction txn  ");
		sb.append(" where txn_no like CONCAT('" + ActionContext.getContext().getSession().get("query").toString() + "','%') ");
		sb.append(" and txn_settle ='0' ");
		querySet.setSqlInfo(sb.toString());
		querySet.setPageInfo(new PageInfo(1, 1));
		formMap.put("txnUnsettle", globalService.search(querySet).getResultList().get(0).get("txnUnsettle"));
		
		querySet = new QuerySet<Object>();
		sb = new StringBuffer();
		sb.append(" select count(*)   'txnLogiced' ");
		sb.append(" from t_transaction txn  ");
		sb.append(" where txn_no like CONCAT('" + ActionContext.getContext().getSession().get("query").toString() + "','%') ");
		sb.append(" and txn_logic ='1' and txn_state='1' ");
		querySet.setSqlInfo(sb.toString());
		querySet.setPageInfo(new PageInfo(1, 1));
		formMap.put("txnLogiced", globalService.search(querySet).getResultList().get(0).get("txnLogiced"));
		
		querySet = new QuerySet<Object>();
		sb = new StringBuffer();
		sb.append(" select count(*)   'txnUnlogic' ");
		sb.append(" from t_transaction txn  ");
		sb.append(" where txn_no like CONCAT('" + ActionContext.getContext().getSession().get("query").toString() + "','%') ");
		sb.append(" and txn_logic ='0' and txn_state='1' ");
		querySet.setSqlInfo(sb.toString());
		querySet.setPageInfo(new PageInfo(1, 1));
		formMap.put("txnUnlogic", globalService.search(querySet).getResultList().get(0).get("txnUnlogic"));

		return SUCCESS;
		
		
	}

	////////////////////// ---------------------------//////////////////////////////////////////////////////////////////////////////////////////////////////////////
	////////////////////// ---------------------------//////////////////////////////////////////////////////////////////////////////////////////////////////////////
	////////////////////// ---------------------------//////////////////////////////////////////////////////////////////////////////////////////////////////////////

	public IGlobalService getGlobalService() {
		return globalService;
	}

	public void setGlobalService(IGlobalService globalService) {
		this.globalService = globalService;
	}

	public List<LinkedHashMap<String, Object>> getFormList() {
		return formList;
	}

	public void setFormList(List<LinkedHashMap<String, Object>> formList) {
		this.formList = formList;
	}

	public Map<String, Object> getFormMap() {
		return formMap;
	}

	public void setFormMap(Map<String, Object> formMap) {
		this.formMap = formMap;
	}

}
